﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Configuration;

namespace ADS2Excel
{
    class SQLite3
    {
        //public static string dbPath = "Data Source =" + Environment.CurrentDirectory + "/Database.db3";
        public static string dbPath = ConfigurationManager.AppSettings["dbPath"];// ConfigurationSettings.AppSettings["dbPath"];
        public static int N = Convert.ToInt32(ConfigurationManager.AppSettings["Number"]);

        public static void CreateTables()
        {
            SQLiteConnection conn = null;
            conn = new SQLiteConnection(dbPath);//创建数据库实例，指定文件位置
            conn.Open();//打开数据库，若文件不存在会自动创建
            string sql = GenerateCreateTableSQL("SJCG_Z", SQLite3.N)
                + GenerateCreateTableSQL("YCCG_Z", SQLite3.N)
                + GenerateCreateTableSQL("SJWD_Z", SQLite3.N)
                + GenerateCreateTableSQL("YCWD_Y", SQLite3.N)
                + GenerateCreateTableSQL("SJCG_Y", SQLite3.N)
                + GenerateCreateTableSQL("YCCG_Y", SQLite3.N)
                + GenerateCreateTableSQL("SJWD_Y", SQLite3.N)
                + GenerateCreateTableSQL("YCWD_Z", SQLite3.N);
            SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, conn);
            cmdCreateTable.ExecuteNonQuery();//如果表不存在，创建数据表  
            conn.Close();
        }

        public static string GenerateCreateTableSQL(string table, int num)
        {
            string temp = " ";
            string s = "";
            for (int i = 1; i <= num; i++)
            {
                s = Convert.ToString(i);
                if (i == num)
                {
                    temp = temp + table + s + " varchar(20));";
                }
                else
                    temp = temp + table + s + " varchar(20),";
            }
            return "CREATE TABLE IF NOT EXISTS " + table + "(time varchar(20)," + temp;
        }

        public static string GenerateInsertSQL(string table, string[] values)
        {
            string temp = " ";
            int len = values.Length;
            int lentemp = len - 1;
            for (int i = 0; i < len; i++)
            {
                if (i == lentemp)
                {
                    temp = temp + "@" + values[i] + ");";
                }
                else
                {
                    temp = temp + "@" + values[i] + ",";
                }
            }
            return "insert into " + table + " values(@time," + temp;
        }

        public static string GenerateInsertSQL(string table, int num)
        {
            string temp = " ";
            string s = "";
            for (int i = 1; i <= num; i++)
            {
                s = Convert.ToString(i);
                if (i == num)
                {
                    temp = temp + "@" + table + s + " );";
                }
                else
                {
                    temp = temp + "@" + table + s + " ,";
                }
            }
            return "insert into " + table + " values(@time," + temp;
        }

        public static DataSet SelectDB(string table)
        {
            string sql = "SELECT * FROM "+table+" ORDER BY time DESC LIMIT 1";
            return SQLiteHelper.ExecuteDataSet(sql);
        }
    }
}
